{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[![AWS SDK for pandas](_static/logo.png \"AWS SDK for pandas\")](https://github.com/aws/aws-sdk-pandas)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 31 - OpenSearch"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Table of Contents\n",
    "* [1. Initialize](#1.-Initialize)\n",
    "    * [Connect to your Amazon OpenSearch domain](#Connect-to-your-Amazon-OpenSearch-domain)\n",
    "    * [Enter your bucket name](#enter-your-bucket-name)\n",
    "    * [Initialize sample data](#initialize-sample-data)\n",
    "* [2. Indexing (load)](#2.-Indexing-(load))\n",
    "\t* [Index documents (no Pandas)](#index-documents-(no-pandas))\n",
    "\t* [Index json file](#index-json-file)\n",
    "    * [Index CSV](#index-csv)\n",
    "* [3. Search](#3.-Search)\n",
    "\t* [Search by DSL](#search-by-dsl)\n",
    "\t* [Search by SQL](#search-by-sql)\n",
    "* [4. Delete Indices](#4.-Delete-Indices)\n",
    "* [5. Bonus - Prepare data and index from DataFrame](#5.-Bonus---Prepare-data-and-index-from-DataFrame)\n",
    "\t* [Prepare the data for indexing](#prepare-the-data-for-indexing)\n",
    "    * [Create index with mapping](#create-index-with-mapping)\n",
    "    * [Index dataframe](#index-dataframe)\n",
    "    * [Execute geo query](#execute-geo-query)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Initialize"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Install the optional modules first\n",
    "!pip install 'awswrangler[opensearch]'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import awswrangler as wr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to your Amazon OpenSearch domain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = wr.opensearch.connect(\n",
    "    host=\"OPENSEARCH-ENDPOINT\",\n",
    "    #     username='FGAC-USERNAME(OPTIONAL)',\n",
    "    #     password='FGAC-PASSWORD(OPTIONAL)'\n",
    ")\n",
    "client.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Enter your bucket name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "bucket = \"BUCKET\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Initialize sample data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "sf_restaurants_inspections = [\n",
    "    {\n",
    "        \"inspection_id\": \"24936_20160609\",\n",
    "        \"business_address\": \"315 California St\",\n",
    "        \"business_city\": \"San Francisco\",\n",
    "        \"business_id\": \"24936\",\n",
    "        \"business_location\": {\"lon\": -122.400152, \"lat\": 37.793199},\n",
    "        \"business_name\": \"San Francisco Soup Company\",\n",
    "        \"business_postal_code\": \"94104\",\n",
    "        \"business_state\": \"CA\",\n",
    "        \"inspection_date\": \"2016-06-09T00:00:00.000\",\n",
    "        \"inspection_score\": 77,\n",
    "        \"inspection_type\": \"Routine - Unscheduled\",\n",
    "        \"risk_category\": \"Low Risk\",\n",
    "        \"violation_description\": \"Improper food labeling or menu misrepresentation\",\n",
    "        \"violation_id\": \"24936_20160609_103141\",\n",
    "    },\n",
    "    {\n",
    "        \"inspection_id\": \"60354_20161123\",\n",
    "        \"business_address\": \"10 Mason St\",\n",
    "        \"business_city\": \"San Francisco\",\n",
    "        \"business_id\": \"60354\",\n",
    "        \"business_location\": {\"lon\": -122.409061, \"lat\": 37.783527},\n",
    "        \"business_name\": \"Soup Unlimited\",\n",
    "        \"business_postal_code\": \"94102\",\n",
    "        \"business_state\": \"CA\",\n",
    "        \"inspection_date\": \"2016-11-23T00:00:00.000\",\n",
    "        \"inspection_type\": \"Routine\",\n",
    "        \"inspection_score\": 95,\n",
    "    },\n",
    "    {\n",
    "        \"inspection_id\": \"1797_20160705\",\n",
    "        \"business_address\": \"2872 24th St\",\n",
    "        \"business_city\": \"San Francisco\",\n",
    "        \"business_id\": \"1797\",\n",
    "        \"business_location\": {\"lon\": -122.409752, \"lat\": 37.752807},\n",
    "        \"business_name\": \"TIO CHILOS GRILL\",\n",
    "        \"business_postal_code\": \"94110\",\n",
    "        \"business_state\": \"CA\",\n",
    "        \"inspection_date\": \"2016-07-05T00:00:00.000\",\n",
    "        \"inspection_score\": 90,\n",
    "        \"inspection_type\": \"Routine - Unscheduled\",\n",
    "        \"risk_category\": \"Low Risk\",\n",
    "        \"violation_description\": \"Unclean nonfood contact surfaces\",\n",
    "        \"violation_id\": \"1797_20160705_103142\",\n",
    "    },\n",
    "    {\n",
    "        \"inspection_id\": \"66198_20160527\",\n",
    "        \"business_address\": \"1661 Tennessee St Suite 3B\",\n",
    "        \"business_city\": \"San Francisco Whard Restaurant\",\n",
    "        \"business_id\": \"66198\",\n",
    "        \"business_location\": {\"lon\": -122.388478, \"lat\": 37.75072},\n",
    "        \"business_name\": \"San Francisco Restaurant\",\n",
    "        \"business_postal_code\": \"94107\",\n",
    "        \"business_state\": \"CA\",\n",
    "        \"inspection_date\": \"2016-05-27T00:00:00.000\",\n",
    "        \"inspection_type\": \"Routine\",\n",
    "        \"inspection_score\": 56,\n",
    "    },\n",
    "    {\n",
    "        \"inspection_id\": \"5794_20160907\",\n",
    "        \"business_address\": \"2162 24th Ave\",\n",
    "        \"business_city\": \"San Francisco\",\n",
    "        \"business_id\": \"5794\",\n",
    "        \"business_location\": {\"lon\": -122.481299, \"lat\": 37.747228},\n",
    "        \"business_name\": \"Soup House\",\n",
    "        \"business_phone_number\": \"+14155752700\",\n",
    "        \"business_postal_code\": \"94116\",\n",
    "        \"business_state\": \"CA\",\n",
    "        \"inspection_date\": \"2016-09-07T00:00:00.000\",\n",
    "        \"inspection_score\": 96,\n",
    "        \"inspection_type\": \"Routine - Unscheduled\",\n",
    "        \"risk_category\": \"Low Risk\",\n",
    "        \"violation_description\": \"Unapproved or unmaintained equipment or utensils\",\n",
    "        \"violation_id\": \"5794_20160907_103144\",\n",
    "    },\n",
    "    # duplicate record\n",
    "    {\n",
    "        \"inspection_id\": \"5794_20160907\",\n",
    "        \"business_address\": \"2162 24th Ave\",\n",
    "        \"business_city\": \"San Francisco\",\n",
    "        \"business_id\": \"5794\",\n",
    "        \"business_location\": {\"lon\": -122.481299, \"lat\": 37.747228},\n",
    "        \"business_name\": \"Soup-or-Salad\",\n",
    "        \"business_phone_number\": \"+14155752700\",\n",
    "        \"business_postal_code\": \"94116\",\n",
    "        \"business_state\": \"CA\",\n",
    "        \"inspection_date\": \"2016-09-07T00:00:00.000\",\n",
    "        \"inspection_score\": 96,\n",
    "        \"inspection_type\": \"Routine - Unscheduled\",\n",
    "        \"risk_category\": \"Low Risk\",\n",
    "        \"violation_description\": \"Unapproved or unmaintained equipment or utensils\",\n",
    "        \"violation_id\": \"5794_20160907_103144\",\n",
    "    },\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Indexing (load)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Index documents (no Pandas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Indexing: 100% (6/6)|####################################|Elapsed Time: 0:00:01"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'success': 6, 'errors': []}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# index documents w/o providing keys (_id is auto-generated)\n",
    "wr.opensearch.index_documents(client, documents=sf_restaurants_inspections, index=\"sf_restaurants_inspections\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>business_name</th>\n",
       "      <th>inspection_id</th>\n",
       "      <th>business_location.lon</th>\n",
       "      <th>business_location.lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>663dd72d-0da4-495b-b0ae-ed000105ae73</td>\n",
       "      <td>TIO CHILOS GRILL</td>\n",
       "      <td>1797_20160705</td>\n",
       "      <td>-122.409752</td>\n",
       "      <td>37.752807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ff2f50f6-5415-4706-9bcb-af7c5eb0afa3</td>\n",
       "      <td>Soup House</td>\n",
       "      <td>5794_20160907</td>\n",
       "      <td>-122.481299</td>\n",
       "      <td>37.747228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b9e8f6a2-8fd1-4660-b041-2997a1a80984</td>\n",
       "      <td>San Francisco Soup Company</td>\n",
       "      <td>24936_20160609</td>\n",
       "      <td>-122.400152</td>\n",
       "      <td>37.793199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>56b352e6-102b-4eff-8296-7e1fb2459bab</td>\n",
       "      <td>Soup Unlimited</td>\n",
       "      <td>60354_20161123</td>\n",
       "      <td>-122.409061</td>\n",
       "      <td>37.783527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6fec5411-f79a-48e4-be7b-e0e44d5ebbab</td>\n",
       "      <td>San Francisco Restaurant</td>\n",
       "      <td>66198_20160527</td>\n",
       "      <td>-122.388478</td>\n",
       "      <td>37.750720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7ba4fb17-f9a9-49da-b90e-8b3553d6d97c</td>\n",
       "      <td>Soup-or-Salad</td>\n",
       "      <td>5794_20160907</td>\n",
       "      <td>-122.481299</td>\n",
       "      <td>37.747228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    _id               business_name  \\\n",
       "0  663dd72d-0da4-495b-b0ae-ed000105ae73            TIO CHILOS GRILL   \n",
       "1  ff2f50f6-5415-4706-9bcb-af7c5eb0afa3                  Soup House   \n",
       "2  b9e8f6a2-8fd1-4660-b041-2997a1a80984  San Francisco Soup Company   \n",
       "3  56b352e6-102b-4eff-8296-7e1fb2459bab              Soup Unlimited   \n",
       "4  6fec5411-f79a-48e4-be7b-e0e44d5ebbab    San Francisco Restaurant   \n",
       "5  7ba4fb17-f9a9-49da-b90e-8b3553d6d97c               Soup-or-Salad   \n",
       "\n",
       "    inspection_id  business_location.lon  business_location.lat  \n",
       "0   1797_20160705            -122.409752              37.752807  \n",
       "1   5794_20160907            -122.481299              37.747228  \n",
       "2  24936_20160609            -122.400152              37.793199  \n",
       "3  60354_20161123            -122.409061              37.783527  \n",
       "4  66198_20160527            -122.388478              37.750720  \n",
       "5   5794_20160907            -122.481299              37.747228  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read all documents. There are total 6 documents\n",
    "wr.opensearch.search(\n",
    "    client, index=\"sf_restaurants_inspections\", _source=[\"inspection_id\", \"business_name\", \"business_location\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Index json file<a class=\"anchor\" id=\"index-json\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame(sf_restaurants_inspections)\n",
    "path = f\"s3://{bucket}/json/sf_restaurants_inspections.json\"\n",
    "wr.s3.to_json(df, path, orient=\"records\", lines=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Indexing: 100% (6/6)|####################################|Elapsed Time: 0:00:00"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'success': 6, 'errors': []}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# index json w/ providing keys\n",
    "wr.opensearch.index_json(\n",
    "    client,\n",
    "    path=path,  # path can be s3 or local\n",
    "    index=\"sf_restaurants_inspections_dedup\",\n",
    "    id_keys=[\"inspection_id\"],  # can be multiple fields. arg applicable to all index_* functions\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>business_name</th>\n",
       "      <th>inspection_id</th>\n",
       "      <th>business_location.lon</th>\n",
       "      <th>business_location.lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24936_20160609</td>\n",
       "      <td>San Francisco Soup Company</td>\n",
       "      <td>24936_20160609</td>\n",
       "      <td>-122.400152</td>\n",
       "      <td>37.793199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>66198_20160527</td>\n",
       "      <td>San Francisco Restaurant</td>\n",
       "      <td>66198_20160527</td>\n",
       "      <td>-122.388478</td>\n",
       "      <td>37.750720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5794_20160907</td>\n",
       "      <td>Soup-or-Salad</td>\n",
       "      <td>5794_20160907</td>\n",
       "      <td>-122.481299</td>\n",
       "      <td>37.747228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>60354_20161123</td>\n",
       "      <td>Soup Unlimited</td>\n",
       "      <td>60354_20161123</td>\n",
       "      <td>-122.409061</td>\n",
       "      <td>37.783527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1797_20160705</td>\n",
       "      <td>TIO CHILOS GRILL</td>\n",
       "      <td>1797_20160705</td>\n",
       "      <td>-122.409752</td>\n",
       "      <td>37.752807</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              _id               business_name   inspection_id  \\\n",
       "0  24936_20160609  San Francisco Soup Company  24936_20160609   \n",
       "1  66198_20160527    San Francisco Restaurant  66198_20160527   \n",
       "2   5794_20160907               Soup-or-Salad   5794_20160907   \n",
       "3  60354_20161123              Soup Unlimited  60354_20161123   \n",
       "4   1797_20160705            TIO CHILOS GRILL   1797_20160705   \n",
       "\n",
       "   business_location.lon  business_location.lat  \n",
       "0            -122.400152              37.793199  \n",
       "1            -122.388478              37.750720  \n",
       "2            -122.481299              37.747228  \n",
       "3            -122.409061              37.783527  \n",
       "4            -122.409752              37.752807  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# now there are no duplicates. There are total 5 documents\n",
    "wr.opensearch.search(\n",
    "    client, index=\"sf_restaurants_inspections_dedup\", _source=[\"inspection_id\", \"business_name\", \"business_location\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Index CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Indexing: 100% (1000/1000)|##############################|Elapsed Time: 0:00:00"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'success': 1000, 'errors': []}"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.opensearch.index_csv(\n",
    "    client,\n",
    "    index=\"nyc_restaurants_inspections_sample\",\n",
    "    path=\"https://data.cityofnewyork.us/api/views/43nn-pn8j/rows.csv?accessType=DOWNLOAD\",  # index_csv supports local, s3 and url path\n",
    "    id_keys=[\"CAMIS\"],\n",
    "    pandas_kwargs={\n",
    "        \"na_filter\": True,\n",
    "        \"nrows\": 1000,\n",
    "    },  # pandas.read_csv() args - https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html\n",
    "    bulk_size=500,  # modify based on your cluster size\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>CAMIS</th>\n",
       "      <th>DBA</th>\n",
       "      <th>BORO</th>\n",
       "      <th>BUILDING</th>\n",
       "      <th>STREET</th>\n",
       "      <th>ZIPCODE</th>\n",
       "      <th>PHONE</th>\n",
       "      <th>CUISINE DESCRIPTION</th>\n",
       "      <th>INSPECTION DATE</th>\n",
       "      <th>...</th>\n",
       "      <th>RECORD DATE</th>\n",
       "      <th>INSPECTION TYPE</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Community Board</th>\n",
       "      <th>Council District</th>\n",
       "      <th>Census Tract</th>\n",
       "      <th>BIN</th>\n",
       "      <th>BBL</th>\n",
       "      <th>NTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41610426</td>\n",
       "      <td>41610426</td>\n",
       "      <td>GLOW THAI RESTAURANT</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>7107</td>\n",
       "      <td>3 AVENUE</td>\n",
       "      <td>11209.0</td>\n",
       "      <td>7187481920</td>\n",
       "      <td>Thai</td>\n",
       "      <td>02/26/2020</td>\n",
       "      <td>...</td>\n",
       "      <td>10/04/2021</td>\n",
       "      <td>Cycle Inspection / Re-inspection</td>\n",
       "      <td>40.633865</td>\n",
       "      <td>-74.026798</td>\n",
       "      <td>310.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>6800.0</td>\n",
       "      <td>3146519.0</td>\n",
       "      <td>3.058910e+09</td>\n",
       "      <td>BK31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>40811162</td>\n",
       "      <td>40811162</td>\n",
       "      <td>CARMINE'S</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>2450</td>\n",
       "      <td>BROADWAY</td>\n",
       "      <td>10024.0</td>\n",
       "      <td>2123622200</td>\n",
       "      <td>Italian</td>\n",
       "      <td>05/28/2019</td>\n",
       "      <td>...</td>\n",
       "      <td>10/04/2021</td>\n",
       "      <td>Cycle Inspection / Initial Inspection</td>\n",
       "      <td>40.791168</td>\n",
       "      <td>-73.974308</td>\n",
       "      <td>107.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>17900.0</td>\n",
       "      <td>1033560.0</td>\n",
       "      <td>1.012380e+09</td>\n",
       "      <td>MN12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50012113</td>\n",
       "      <td>50012113</td>\n",
       "      <td>TANG</td>\n",
       "      <td>Queens</td>\n",
       "      <td>196-50</td>\n",
       "      <td>NORTHERN BOULEVARD</td>\n",
       "      <td>11358.0</td>\n",
       "      <td>7182797080</td>\n",
       "      <td>Korean</td>\n",
       "      <td>08/16/2018</td>\n",
       "      <td>...</td>\n",
       "      <td>10/04/2021</td>\n",
       "      <td>Cycle Inspection / Initial Inspection</td>\n",
       "      <td>40.757850</td>\n",
       "      <td>-73.784593</td>\n",
       "      <td>411.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>145101.0</td>\n",
       "      <td>4124565.0</td>\n",
       "      <td>4.055200e+09</td>\n",
       "      <td>QN48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50014618</td>\n",
       "      <td>50014618</td>\n",
       "      <td>TOTTO RAMEN</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>248</td>\n",
       "      <td>EAST   52 STREET</td>\n",
       "      <td>10022.0</td>\n",
       "      <td>2124210052</td>\n",
       "      <td>Japanese</td>\n",
       "      <td>08/20/2018</td>\n",
       "      <td>...</td>\n",
       "      <td>10/04/2021</td>\n",
       "      <td>Cycle Inspection / Re-inspection</td>\n",
       "      <td>40.756596</td>\n",
       "      <td>-73.968749</td>\n",
       "      <td>106.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9800.0</td>\n",
       "      <td>1038490.0</td>\n",
       "      <td>1.013250e+09</td>\n",
       "      <td>MN19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50045782</td>\n",
       "      <td>50045782</td>\n",
       "      <td>OLLIE'S CHINESE RESTAURANT</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>2705</td>\n",
       "      <td>BROADWAY</td>\n",
       "      <td>10025.0</td>\n",
       "      <td>2129323300</td>\n",
       "      <td>Chinese</td>\n",
       "      <td>10/21/2019</td>\n",
       "      <td>...</td>\n",
       "      <td>10/04/2021</td>\n",
       "      <td>Cycle Inspection / Re-inspection</td>\n",
       "      <td>40.799318</td>\n",
       "      <td>-73.968440</td>\n",
       "      <td>107.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>19100.0</td>\n",
       "      <td>1056562.0</td>\n",
       "      <td>1.018750e+09</td>\n",
       "      <td>MN12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        _id     CAMIS                         DBA       BORO BUILDING  \\\n",
       "0  41610426  41610426        GLOW THAI RESTAURANT   Brooklyn     7107   \n",
       "1  40811162  40811162                   CARMINE'S  Manhattan     2450   \n",
       "2  50012113  50012113                        TANG     Queens   196-50   \n",
       "3  50014618  50014618                 TOTTO RAMEN  Manhattan      248   \n",
       "4  50045782  50045782  OLLIE'S CHINESE RESTAURANT  Manhattan     2705   \n",
       "\n",
       "               STREET  ZIPCODE       PHONE CUISINE DESCRIPTION  \\\n",
       "0            3 AVENUE  11209.0  7187481920                Thai   \n",
       "1            BROADWAY  10024.0  2123622200             Italian   \n",
       "2  NORTHERN BOULEVARD  11358.0  7182797080              Korean   \n",
       "3    EAST   52 STREET  10022.0  2124210052            Japanese   \n",
       "4            BROADWAY  10025.0  2129323300             Chinese   \n",
       "\n",
       "  INSPECTION DATE  ... RECORD DATE                        INSPECTION TYPE  \\\n",
       "0      02/26/2020  ...  10/04/2021       Cycle Inspection / Re-inspection   \n",
       "1      05/28/2019  ...  10/04/2021  Cycle Inspection / Initial Inspection   \n",
       "2      08/16/2018  ...  10/04/2021  Cycle Inspection / Initial Inspection   \n",
       "3      08/20/2018  ...  10/04/2021       Cycle Inspection / Re-inspection   \n",
       "4      10/21/2019  ...  10/04/2021       Cycle Inspection / Re-inspection   \n",
       "\n",
       "    Latitude  Longitude  Community Board Council District Census Tract  \\\n",
       "0  40.633865 -74.026798            310.0             43.0       6800.0   \n",
       "1  40.791168 -73.974308            107.0              6.0      17900.0   \n",
       "2  40.757850 -73.784593            411.0             19.0     145101.0   \n",
       "3  40.756596 -73.968749            106.0              4.0       9800.0   \n",
       "4  40.799318 -73.968440            107.0              6.0      19100.0   \n",
       "\n",
       "         BIN           BBL   NTA  \n",
       "0  3146519.0  3.058910e+09  BK31  \n",
       "1  1033560.0  1.012380e+09  MN12  \n",
       "2  4124565.0  4.055200e+09  QN48  \n",
       "3  1038490.0  1.013250e+09  MN19  \n",
       "4  1056562.0  1.018750e+09  MN12  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.opensearch.search(client, index=\"nyc_restaurants_inspections_sample\", size=5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Search\n",
    "Search results are returned as Pandas DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Search by DSL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_id</th>\n",
       "      <th>business_name</th>\n",
       "      <th>inspection_id</th>\n",
       "      <th>business_location.lon</th>\n",
       "      <th>business_location.lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ff2f50f6-5415-4706-9bcb-af7c5eb0afa3</td>\n",
       "      <td>Soup House</td>\n",
       "      <td>5794_20160907</td>\n",
       "      <td>-122.481299</td>\n",
       "      <td>37.747228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7ba4fb17-f9a9-49da-b90e-8b3553d6d97c</td>\n",
       "      <td>Soup-or-Salad</td>\n",
       "      <td>5794_20160907</td>\n",
       "      <td>-122.481299</td>\n",
       "      <td>37.747228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>b9e8f6a2-8fd1-4660-b041-2997a1a80984</td>\n",
       "      <td>San Francisco Soup Company</td>\n",
       "      <td>24936_20160609</td>\n",
       "      <td>-122.400152</td>\n",
       "      <td>37.793199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>56b352e6-102b-4eff-8296-7e1fb2459bab</td>\n",
       "      <td>Soup Unlimited</td>\n",
       "      <td>60354_20161123</td>\n",
       "      <td>-122.409061</td>\n",
       "      <td>37.783527</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    _id               business_name  \\\n",
       "0  ff2f50f6-5415-4706-9bcb-af7c5eb0afa3                  Soup House   \n",
       "1  7ba4fb17-f9a9-49da-b90e-8b3553d6d97c               Soup-or-Salad   \n",
       "2  b9e8f6a2-8fd1-4660-b041-2997a1a80984  San Francisco Soup Company   \n",
       "3  56b352e6-102b-4eff-8296-7e1fb2459bab              Soup Unlimited   \n",
       "\n",
       "    inspection_id  business_location.lon  business_location.lat  \n",
       "0   5794_20160907            -122.481299              37.747228  \n",
       "1   5794_20160907            -122.481299              37.747228  \n",
       "2  24936_20160609            -122.400152              37.793199  \n",
       "3  60354_20161123            -122.409061              37.783527  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add a search query. search all soup businesses\n",
    "wr.opensearch.search(\n",
    "    client,\n",
    "    index=\"sf_restaurants_inspections\",\n",
    "    _source=[\"inspection_id\", \"business_name\", \"business_location\"],\n",
    "    filter_path=[\"hits.hits._id\", \"hits.hits._source\"],\n",
    "    search_body={\"query\": {\"match\": {\"business_name\": \"soup\"}}},\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Search by SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_index</th>\n",
       "      <th>_type</th>\n",
       "      <th>_id</th>\n",
       "      <th>_score</th>\n",
       "      <th>business_name</th>\n",
       "      <th>inspection_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>sf_restaurants_inspections_dedup</td>\n",
       "      <td>_doc</td>\n",
       "      <td>5794_20160907</td>\n",
       "      <td>None</td>\n",
       "      <td>Soup-or-Salad</td>\n",
       "      <td>96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>sf_restaurants_inspections_dedup</td>\n",
       "      <td>_doc</td>\n",
       "      <td>60354_20161123</td>\n",
       "      <td>None</td>\n",
       "      <td>Soup Unlimited</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>sf_restaurants_inspections_dedup</td>\n",
       "      <td>_doc</td>\n",
       "      <td>24936_20160609</td>\n",
       "      <td>None</td>\n",
       "      <td>San Francisco Soup Company</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             _index _type             _id _score  \\\n",
       "0  sf_restaurants_inspections_dedup  _doc   5794_20160907   None   \n",
       "1  sf_restaurants_inspections_dedup  _doc  60354_20161123   None   \n",
       "2  sf_restaurants_inspections_dedup  _doc  24936_20160609   None   \n",
       "\n",
       "                business_name  inspection_score  \n",
       "0               Soup-or-Salad                96  \n",
       "1              Soup Unlimited                95  \n",
       "2  San Francisco Soup Company                77  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.opensearch.search_by_sql(\n",
    "    client,\n",
    "    sql_query=\"\"\"SELECT business_name, inspection_score\n",
    "                    FROM sf_restaurants_inspections_dedup\n",
    "                    WHERE business_name LIKE '%soup%'\n",
    "                    ORDER BY inspection_score DESC LIMIT 5\"\"\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Delete Indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'acknowledged': True}"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.opensearch.delete_index(client=client, index=\"sf_restaurants_inspections\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Bonus - Prepare data and index from DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For this exercise we'll use [DOHMH New York City Restaurant Inspection Results dataset](https://data.cityofnewyork.us/Health/DOHMH-New-York-City-Restaurant-Inspection-Results/43nn-pn8j)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"https://data.cityofnewyork.us/api/views/43nn-pn8j/rows.csv?accessType=DOWNLOAD\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare the data for indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fields names underscore casing\n",
    "df.columns = [col.lower().replace(\" \", \"_\") for col in df.columns]\n",
    "\n",
    "# convert lon/lat to OpenSearch geo_point\n",
    "df[\"business_location\"] = (\n",
    "    \"POINT (\" + df.longitude.fillna(\"0\").astype(str) + \" \" + df.latitude.fillna(\"0\").astype(str) + \")\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create index with mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'acknowledged': True,\n",
       " 'shards_acknowledged': True,\n",
       " 'index': 'nyc_restaurants_inspections'}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# delete index if exists\n",
    "wr.opensearch.delete_index(client=client, index=\"nyc_restaurants\")\n",
    "\n",
    "# use dynamic_template to map date fields\n",
    "# define business_location as geo_point\n",
    "wr.opensearch.create_index(\n",
    "    client=client,\n",
    "    index=\"nyc_restaurants_inspections\",\n",
    "    mappings={\n",
    "        \"dynamic_templates\": [{\"dates\": {\"match\": \"*date\", \"mapping\": {\"type\": \"date\", \"format\": \"MM/dd/yyyy\"}}}],\n",
    "        \"properties\": {\"business_location\": {\"type\": \"geo_point\"}},\n",
    "    },\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Index dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Indexing: 100% (382655/382655)|##########################|Elapsed Time: 0:04:15"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'success': 382655, 'errors': []}"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.opensearch.index_df(client, df=df, index=\"nyc_restaurants_inspections\", id_keys=[\"camis\"], bulk_size=1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Execute geo query\n",
    "#### Sort restaurants by distance from Times-Square"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>camis</th>\n",
       "      <th>dba</th>\n",
       "      <th>boro</th>\n",
       "      <th>building</th>\n",
       "      <th>street</th>\n",
       "      <th>zipcode</th>\n",
       "      <th>phone</th>\n",
       "      <th>cuisine_description</th>\n",
       "      <th>inspection_date</th>\n",
       "      <th>action</th>\n",
       "      <th>...</th>\n",
       "      <th>inspection_type</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>community_board</th>\n",
       "      <th>council_district</th>\n",
       "      <th>census_tract</th>\n",
       "      <th>bin</th>\n",
       "      <th>bbl</th>\n",
       "      <th>nta</th>\n",
       "      <th>business_location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41551304</td>\n",
       "      <td>THE COUNTER</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>7</td>\n",
       "      <td>TIMES SQUARE</td>\n",
       "      <td>10036.0</td>\n",
       "      <td>2129976801</td>\n",
       "      <td>American</td>\n",
       "      <td>12/22/2016</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>...</td>\n",
       "      <td>Cycle Inspection / Initial Inspection</td>\n",
       "      <td>40.755908</td>\n",
       "      <td>-73.986681</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11300.0</td>\n",
       "      <td>1086069.0</td>\n",
       "      <td>1.009940e+09</td>\n",
       "      <td>MN17</td>\n",
       "      <td>POINT (-73.986680953809 40.755907817312)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50055665</td>\n",
       "      <td>ANN INC CAFE</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>7</td>\n",
       "      <td>TIMES SQUARE</td>\n",
       "      <td>10036.0</td>\n",
       "      <td>2125413287</td>\n",
       "      <td>American</td>\n",
       "      <td>12/11/2019</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>...</td>\n",
       "      <td>Cycle Inspection / Initial Inspection</td>\n",
       "      <td>40.755908</td>\n",
       "      <td>-73.986681</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11300.0</td>\n",
       "      <td>1086069.0</td>\n",
       "      <td>1.009940e+09</td>\n",
       "      <td>MN17</td>\n",
       "      <td>POINT (-73.986680953809 40.755907817312)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50049552</td>\n",
       "      <td>ERNST AND YOUNG</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>5</td>\n",
       "      <td>TIMES SQ</td>\n",
       "      <td>10036.0</td>\n",
       "      <td>2127739994</td>\n",
       "      <td>Coffee/Tea</td>\n",
       "      <td>11/30/2018</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>...</td>\n",
       "      <td>Cycle Inspection / Initial Inspection</td>\n",
       "      <td>40.755702</td>\n",
       "      <td>-73.987208</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11300.0</td>\n",
       "      <td>1024656.0</td>\n",
       "      <td>1.010130e+09</td>\n",
       "      <td>MN17</td>\n",
       "      <td>POINT (-73.987207980138 40.755702020307)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50014078</td>\n",
       "      <td>RED LOBSTER</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>5</td>\n",
       "      <td>TIMES SQ</td>\n",
       "      <td>10036.0</td>\n",
       "      <td>2127306706</td>\n",
       "      <td>Seafood</td>\n",
       "      <td>10/03/2017</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>...</td>\n",
       "      <td>Cycle Inspection / Initial Inspection</td>\n",
       "      <td>40.755702</td>\n",
       "      <td>-73.987208</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11300.0</td>\n",
       "      <td>1024656.0</td>\n",
       "      <td>1.010130e+09</td>\n",
       "      <td>MN17</td>\n",
       "      <td>POINT (-73.987207980138 40.755702020307)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50015171</td>\n",
       "      <td>NEW AMSTERDAM THEATER</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>214</td>\n",
       "      <td>WEST   42 STREET</td>\n",
       "      <td>10036.0</td>\n",
       "      <td>2125825472</td>\n",
       "      <td>American</td>\n",
       "      <td>06/26/2018</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>...</td>\n",
       "      <td>Cycle Inspection / Re-inspection</td>\n",
       "      <td>40.756317</td>\n",
       "      <td>-73.987652</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11300.0</td>\n",
       "      <td>1024660.0</td>\n",
       "      <td>1.010130e+09</td>\n",
       "      <td>MN17</td>\n",
       "      <td>POINT (-73.987651832547 40.756316895053)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>41552060</td>\n",
       "      <td>PROSKAUER ROSE</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>11</td>\n",
       "      <td>TIMES SQUARE</td>\n",
       "      <td>10036.0</td>\n",
       "      <td>2129695493</td>\n",
       "      <td>American</td>\n",
       "      <td>08/11/2017</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>...</td>\n",
       "      <td>Administrative Miscellaneous / Initial Inspection</td>\n",
       "      <td>40.756891</td>\n",
       "      <td>-73.990023</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11300.0</td>\n",
       "      <td>1087978.0</td>\n",
       "      <td>1.010138e+09</td>\n",
       "      <td>MN17</td>\n",
       "      <td>POINT (-73.990023200823 40.756890780426)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>41242148</td>\n",
       "      <td>GABBY O'HARA'S</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>123</td>\n",
       "      <td>WEST   39 STREET</td>\n",
       "      <td>10018.0</td>\n",
       "      <td>2122788984</td>\n",
       "      <td>Irish</td>\n",
       "      <td>07/30/2019</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>...</td>\n",
       "      <td>Cycle Inspection / Re-inspection</td>\n",
       "      <td>40.753405</td>\n",
       "      <td>-73.986602</td>\n",
       "      <td>105.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>11300.0</td>\n",
       "      <td>1080611.0</td>\n",
       "      <td>1.008150e+09</td>\n",
       "      <td>MN17</td>\n",
       "      <td>POINT (-73.986602050292 40.753404587174)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>50095860</td>\n",
       "      <td>THE TIMES EATERY</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>680</td>\n",
       "      <td>8 AVENUE</td>\n",
       "      <td>10036.0</td>\n",
       "      <td>6463867787</td>\n",
       "      <td>American</td>\n",
       "      <td>02/28/2020</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>...</td>\n",
       "      <td>Pre-permit (Operational) / Initial Inspection</td>\n",
       "      <td>40.757991</td>\n",
       "      <td>-73.989218</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11900.0</td>\n",
       "      <td>1024703.0</td>\n",
       "      <td>1.010150e+09</td>\n",
       "      <td>MN17</td>\n",
       "      <td>POINT (-73.989218092096 40.757991356019)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>50072861</td>\n",
       "      <td>ITSU</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>530</td>\n",
       "      <td>7 AVENUE</td>\n",
       "      <td>10018.0</td>\n",
       "      <td>9176393645</td>\n",
       "      <td>Asian/Asian Fusion</td>\n",
       "      <td>09/10/2018</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>...</td>\n",
       "      <td>Pre-permit (Operational) / Initial Inspection</td>\n",
       "      <td>40.753844</td>\n",
       "      <td>-73.988551</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11300.0</td>\n",
       "      <td>1014485.0</td>\n",
       "      <td>1.007880e+09</td>\n",
       "      <td>MN17</td>\n",
       "      <td>POINT (-73.988551029682 40.753843959794)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>50068109</td>\n",
       "      <td>LUKE'S LOBSTER</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>1407</td>\n",
       "      <td>BROADWAY</td>\n",
       "      <td>10018.0</td>\n",
       "      <td>9174759192</td>\n",
       "      <td>Seafood</td>\n",
       "      <td>09/06/2017</td>\n",
       "      <td>Violations were cited in the following area(s).</td>\n",
       "      <td>...</td>\n",
       "      <td>Pre-permit (Operational) / Initial Inspection</td>\n",
       "      <td>40.753432</td>\n",
       "      <td>-73.987151</td>\n",
       "      <td>105.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>11300.0</td>\n",
       "      <td>1015265.0</td>\n",
       "      <td>1.008140e+09</td>\n",
       "      <td>MN17</td>\n",
       "      <td>POINT (-73.98715066791 40.753432097521)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       camis                    dba       boro building            street  \\\n",
       "0   41551304            THE COUNTER  Manhattan        7      TIMES SQUARE   \n",
       "1   50055665           ANN INC CAFE  Manhattan        7      TIMES SQUARE   \n",
       "2   50049552        ERNST AND YOUNG  Manhattan        5          TIMES SQ   \n",
       "3   50014078            RED LOBSTER  Manhattan        5          TIMES SQ   \n",
       "4   50015171  NEW AMSTERDAM THEATER  Manhattan      214  WEST   42 STREET   \n",
       "..       ...                    ...        ...      ...               ...   \n",
       "95  41552060         PROSKAUER ROSE  Manhattan       11      TIMES SQUARE   \n",
       "96  41242148         GABBY O'HARA'S  Manhattan      123  WEST   39 STREET   \n",
       "97  50095860       THE TIMES EATERY  Manhattan      680          8 AVENUE   \n",
       "98  50072861                   ITSU  Manhattan      530          7 AVENUE   \n",
       "99  50068109         LUKE'S LOBSTER  Manhattan     1407          BROADWAY   \n",
       "\n",
       "    zipcode       phone cuisine_description inspection_date  \\\n",
       "0   10036.0  2129976801            American      12/22/2016   \n",
       "1   10036.0  2125413287            American      12/11/2019   \n",
       "2   10036.0  2127739994          Coffee/Tea      11/30/2018   \n",
       "3   10036.0  2127306706             Seafood      10/03/2017   \n",
       "4   10036.0  2125825472            American      06/26/2018   \n",
       "..      ...         ...                 ...             ...   \n",
       "95  10036.0  2129695493            American      08/11/2017   \n",
       "96  10018.0  2122788984               Irish      07/30/2019   \n",
       "97  10036.0  6463867787            American      02/28/2020   \n",
       "98  10018.0  9176393645  Asian/Asian Fusion      09/10/2018   \n",
       "99  10018.0  9174759192             Seafood      09/06/2017   \n",
       "\n",
       "                                             action  ...  \\\n",
       "0   Violations were cited in the following area(s).  ...   \n",
       "1   Violations were cited in the following area(s).  ...   \n",
       "2   Violations were cited in the following area(s).  ...   \n",
       "3   Violations were cited in the following area(s).  ...   \n",
       "4   Violations were cited in the following area(s).  ...   \n",
       "..                                              ...  ...   \n",
       "95  Violations were cited in the following area(s).  ...   \n",
       "96  Violations were cited in the following area(s).  ...   \n",
       "97  Violations were cited in the following area(s).  ...   \n",
       "98  Violations were cited in the following area(s).  ...   \n",
       "99  Violations were cited in the following area(s).  ...   \n",
       "\n",
       "                                      inspection_type   latitude  longitude  \\\n",
       "0               Cycle Inspection / Initial Inspection  40.755908 -73.986681   \n",
       "1               Cycle Inspection / Initial Inspection  40.755908 -73.986681   \n",
       "2               Cycle Inspection / Initial Inspection  40.755702 -73.987208   \n",
       "3               Cycle Inspection / Initial Inspection  40.755702 -73.987208   \n",
       "4                    Cycle Inspection / Re-inspection  40.756317 -73.987652   \n",
       "..                                                ...        ...        ...   \n",
       "95  Administrative Miscellaneous / Initial Inspection  40.756891 -73.990023   \n",
       "96                   Cycle Inspection / Re-inspection  40.753405 -73.986602   \n",
       "97      Pre-permit (Operational) / Initial Inspection  40.757991 -73.989218   \n",
       "98      Pre-permit (Operational) / Initial Inspection  40.753844 -73.988551   \n",
       "99      Pre-permit (Operational) / Initial Inspection  40.753432 -73.987151   \n",
       "\n",
       "    community_board council_district census_tract        bin           bbl  \\\n",
       "0             105.0              3.0      11300.0  1086069.0  1.009940e+09   \n",
       "1             105.0              3.0      11300.0  1086069.0  1.009940e+09   \n",
       "2             105.0              3.0      11300.0  1024656.0  1.010130e+09   \n",
       "3             105.0              3.0      11300.0  1024656.0  1.010130e+09   \n",
       "4             105.0              3.0      11300.0  1024660.0  1.010130e+09   \n",
       "..              ...              ...          ...        ...           ...   \n",
       "95            105.0              3.0      11300.0  1087978.0  1.010138e+09   \n",
       "96            105.0              4.0      11300.0  1080611.0  1.008150e+09   \n",
       "97            105.0              3.0      11900.0  1024703.0  1.010150e+09   \n",
       "98            105.0              3.0      11300.0  1014485.0  1.007880e+09   \n",
       "99            105.0              3.0      11300.0  1015265.0  1.008140e+09   \n",
       "\n",
       "     nta                         business_location  \n",
       "0   MN17  POINT (-73.986680953809 40.755907817312)  \n",
       "1   MN17  POINT (-73.986680953809 40.755907817312)  \n",
       "2   MN17  POINT (-73.987207980138 40.755702020307)  \n",
       "3   MN17  POINT (-73.987207980138 40.755702020307)  \n",
       "4   MN17  POINT (-73.987651832547 40.756316895053)  \n",
       "..   ...                                       ...  \n",
       "95  MN17  POINT (-73.990023200823 40.756890780426)  \n",
       "96  MN17  POINT (-73.986602050292 40.753404587174)  \n",
       "97  MN17  POINT (-73.989218092096 40.757991356019)  \n",
       "98  MN17  POINT (-73.988551029682 40.753843959794)  \n",
       "99  MN17   POINT (-73.98715066791 40.753432097521)  \n",
       "\n",
       "[100 rows x 27 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.opensearch.search(\n",
    "    client,\n",
    "    index=\"nyc_restaurants_inspections\",\n",
    "    filter_path=[\"hits.hits._source\"],\n",
    "    size=100,\n",
    "    search_body={\n",
    "        \"query\": {\"match_all\": {}},\n",
    "        \"sort\": [\n",
    "            {\n",
    "                \"_geo_distance\": {\n",
    "                    \"business_location\": {  # Times-Square - https://geojson.io/#map=16/40.7563/-73.9862\n",
    "                        \"lat\": 40.75613228383523,\n",
    "                        \"lon\": -73.9865791797638,\n",
    "                    },\n",
    "                    \"order\": \"asc\",\n",
    "                }\n",
    "            }\n",
    "        ],\n",
    "    },\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.14",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
